package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

@Service
public class SwDailyInspectionService {
	
	@Autowired
	DomainService domainService;
	
	public List<RowBase> getDailyInspectionList(Map<String, String> params,PageInfo pageInfo) throws Exception{
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
	    String sql = "select * from (select t.*,su.c_userealname,og.group_name,og.GROUP_LEADER,ms.wwtp_id,"
				+ " ms.name,ms.OUNAME,ms.s_domaincode as f_type,ms.s_domaincaption as flow_type,"
				+ " ms.wwtp_name,ms.station_category from (select oir.*,ot.station_id,ot.operation_group_id,"
				+ " ot.start_time,to_char(ot.start_time,'iw') WeekNumber,ot.finish_time,ot.end_time,ot.finish_by from (select o_r.*,t1.s_domaincaption as item_name,t2.s_domaincaption as pitem_name from OP_RECO_RPT o_r" 
                + " left join (select s_domaincode,s_domaincaption,s_id,s_pid from data_defulatdomain where s_domainname='地表水运维项') t1 on o_r.item=t1.s_domaincode"
                + " left join (select s_domaincode,s_domaincaption,s_id,s_pid from data_defulatdomain where s_domainname='地表水运维项') t2 on t1.s_pid=t2.s_id) oir"
				+ " left join operation_task ot on oir.task_code=ot.task_code) t"
				+ " left join (select og1.*,tt1.c_userealname as GROUP_LEADER from OC_GROUP og1 "
				+ " left join (select distinct o1.group_id,o1.group_head,su1.c_userealname from OC_GROUP_ASSO o1"
				+ " left join safe_user su1 on o1.user_id=su1.s_userid where o1.group_head='1') tt1 on og1.guid = tt1.group_id)"
				+ " og on t.operation_group_id = og.guid"
				+ " left join safe_user su on t.finish_by = su.s_userid"
				+ " left join (select s.name, s.wwtp_id, s.code, s.station_category, j.s_domaincode, "
				+ " j.s_domaincaption,p.name as wwtp_name,p.OUNAME"
				+ " from BC_SITE_INFO s" + " left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain"
				+ " where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa "
				+ " left join operations_unit ou on pwa.operator=ou.guid) p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code) fr where 1=1";
 

		if(params.containsKey("task_code")){
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		}else{
			if(params.containsKey("start")){
				sql += " and fr.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("end")){
				sql += " and fr.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("wwtp_id")) {
				sql += " and fr.wwtp_id = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", params.get("wwtp_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and fr.f_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and fr.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("station_id")) {
				sql += " and fr.station_id = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
		}
		
		if(params.containsKey("pitem_name")){
			sql += " and fr.pitem_name=@pitem_name";
			listParam.add(new GlobalParameter("pitem_name", params.get("pitem_name")));
		}
		
		List<RowBase> list = DataUtil.getPageRow(sql, listParam, null, pageInfo);
		
		return list;
	}
	
	public List<RowBase> getSupplyWaterDailyInspectionList(Map<String, String> params,PageInfo pageInfo) throws Exception{
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select * from (select t.*,su.c_userealname,og.group_name,og.GROUP_LEADER,ms.wwtp_id,"
				+ " ms.name,ms.OUNAME,ms.s_domaincode as f_type,ms.s_domaincaption as flow_type,"
				+ " ms.wwtp_name,ms.station_category from (select oir.*,ot.station_id,ot.operation_group_id,"
				+ " ot.start_time,ot.finish_time,ot.end_time,ot.finish_by from (select o_r.*,t1.s_domaincaption as item_name,t2.s_domaincaption as pitem_name from OP_RECO_RPT o_r" 
                + " left join (select s_domaincode,s_domaincaption,s_id,s_pid from data_defulatdomain where s_domainname='地表水运维项') t1 on o_r.item=t1.s_domaincode"
                + " left join (select s_domaincode,s_domaincaption,s_id,s_pid from data_defulatdomain where s_domainname='地表水运维项') t2 on t1.s_pid=t2.s_id) oir"
				+ " left join operation_task ot on oir.task_code=ot.task_code) t"
				+ " left join (select og1.*,tt1.c_userealname as GROUP_LEADER from OC_GROUP og1 "
				+ " left join (select distinct o1.group_id,o1.group_head,su1.c_userealname from OC_GROUP_ASSO o1"
				+ " left join safe_user su1 on o1.user_id=su1.s_userid where o1.group_head='1') tt1 on og1.guid = tt1.group_id)"
				+ " og on t.operation_group_id = og.guid"
				+ " left join safe_user su on t.finish_by = su.s_userid"
				+ " left join (select s.name, s.wwtp_id, s.code, s.station_category, j.s_domaincode, "
				+ " j.s_domaincaption,p.name as wwtp_name,p.OUNAME"
				+ " from BC_SITE_INFO s" + " left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain"
				+ " where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa "
				+ " left join operations_unit ou on pwa.operator=ou.guid) p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code) fr where 1=1";

		if(params.containsKey("task_code")){
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		}else{
			if(params.containsKey("start")){
				sql += " and fr.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("end")){
				sql += " and fr.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("wwtp_id")) {
				sql += " and fr.wwtp_id = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", params.get("wwtp_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and fr.f_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and fr.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("station_id")) {
				sql += " and fr.station_id = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
		}
		
		if(params.containsKey("pitem_name")){
			sql += " and fr.pitem_name=@pitem_name";
			listParam.add(new GlobalParameter("pitem_name", params.get("pitem_name")));
		}
		
		List<RowBase> list = DataUtil.getPageRow(sql, listParam, null, pageInfo);
		
		return list;
	}
	
	public List<RowBase> getDailyInspectionListOld(Map<String, String> params,PageInfo pageInfo) throws Exception{
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select * from (select t.*,su.c_userealname,og.group_name,og.GROUP_LEADER,ms.wwtp_id,ms.name,ms.OUNAME,ms.s_domaincode as f_type,ms.s_domaincaption as flow_type,ms.wwtp_name,ms.station_category from (select oir.*,ot.station_id,ot.operation_group_id,ot.start_time,ot.finish_time,ot.end_time,ot.finish_by from OP_INSP_RECORD oir"
				+ " left join operation_task ot on oir.task_code=ot.task_code) t"
				+ " left join (select og1.*,tt1.c_userealname as GROUP_LEADER from OC_GROUP og1 left join (select distinct o1.group_id,o1.group_head,su1.c_userealname from OC_GROUP_ASSO o1"
				+ " left join safe_user su1 on o1.user_id=su1.s_userid where o1.group_head='1') tt1 on og1.guid = tt1.group_id) og on t.operation_group_id = og.guid"
				+ " left join safe_user su on t.finish_by = su.s_userid"
				+ " left join (select s.name, s.wwtp_id, s.code, s.station_category, j.s_domaincode, j.s_domaincaption,p.name as wwtp_name,p.OUNAME"
				+ " from BC_SITE_INFO s" + " left join (select s_domaincode, s_domaincaption"
				+ " from data_defulatdomain"
				+ " where s_domainname = 'STATION_FLOW_NAME') j on j.s_domaincode=s.flow_type"
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa left join operations_unit ou on pwa.operator=ou.guid) p on s.wwtp_id = p.exp_no) ms on t.station_id=ms.code) fr where 1=1";
		
		if(params.containsKey("task_code")){
			sql += " and fr.task_code = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		}else{
			if(params.containsKey("start")){
				sql += " and fr.start_time >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("end")){
				sql += " and fr.start_time <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("wwtp_id")) {
				sql += " and fr.wwtp_id = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", params.get("wwtp_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and fr.f_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and fr.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("station_id")) {
				sql += " and fr.station_id = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
		}
		
		return DataUtil.getPageRow(sql, listParam,null,pageInfo);
	}
	
	/**
	 * 获取日常巡检任务数据记录列表,用户有效性审核
	 * 
	 * @param params 参数需要包含：STATION_ID, JOB_ID:日常巡检的JOB_ID, START_TIME, END_TIME
	 * @return
	 */
	public List<RowBase> getDailyInspectionDataList(Map<String, String> params) {
		if (params == null)
			return null;

		String strSTATION_ID = params.get("STATION_ID");
		String strJOB_ID = params.get("JOB_ID");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");
		if (StringUtil.IsNullOrEmpty(strSTATION_ID) 
				|| StringUtil.IsNullOrEmpty(strJOB_ID))
			return null;

		String strSQL = " select t.TASK_CODE, t.TASK_STATUS, t.START_TIME as TASK_START_TIME, t.END_TIME as TASK_END_TIME, t.FINISH_TIME as TASK_FINISH_TIME,  \n"
				+ " t1.*, \n" + " t2.NAME as STATION_NAME, \n" + " t3.GROUP_NAME as GROUP_NAME, \n"
				+ " t4.C_USEREALNAME as USER_NAME \n" + " from OP_TASK t \n"
				+ " inner join OP_INSP_RECORD t1 ON t1.TASK_CODE = t.TASK_CODE \n" // dif
				+ " left join BC_SITE_INFO t2 on t2.EXP_NO = t.STATION_ID \n"
				+ " left join OC_GROUP t3 on t3.GUID = t.OPERATION_GROUP_ID \n"
				+ " left join SAFE_USER t4 on t4.S_USERID = t.FINISH_BY \n" + " where t.STATION_ID = '" + strSTATION_ID
				+ "' \n" + " AND t.JOB_ID = '" + strJOB_ID + "' \n"
				// + " t1.PARAM = '" + strPARAM_ID + "' \n" //dif
				+ " AND t.START_TIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND t.START_TIME <= to_date('" + strEND_TIME + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " order by t.START_TIME desc ";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, null, pTable);
		return rcdList;
	}
}
